*******************************************************************************
***
*** Create the dataset on lenders' funding composition
***
*** Dependencies:
***		- URTEVIRK_FIRE_FIRM_03-18.dta 	(in ${procdata})
***		- urtepers_panel_AL.dta 		(in ${procdata})
***		- macro_data.dta 				(in ${procdata})
***		- experian.dta 					(in ${rawdata})
***		- DK_USD.dta 					(in ${rawdata})
***
***	Output to folder ${procdata}:
***		- final_Experian.dta 
***
*******************************************************************************

///// Create list of unique lender cvrs who are active in corporate or household lending 
use cvrnr_lender nonbank_lender using "${procdata}/URTEVIRK_FIRE_FIRM_03-18.dta", clear // Corporate credit
gduplicates drop cvrnr_lender, force 
rename cvrnr_lender cvrnr
keep cvrnr nonbank_lender
tempfile virk
save `virk'

use cvrnr nonbank_lender using "${procdata}/urtepers_panel_ALL.dta", clear // Household credit
gduplicates drop cvrnr, force 
keep cvrnr nonbank_lender

append using `virk'
gduplicates drop cvrnr, force 
drop if mi(nonbank_lender)

// Merge active lenders with balance sheet data
merge 1:m cvrnr using "${rawdata}/experian.dta", keep(match) 
rename cvrnr cvrnr_lender



////////////////////////////////////////////
//////////   Clean Experian data 

gen year = year(afsd)
keep if inrange(year, 2002,2015)
drop year

**************
**    Deal with reporting year 

sort cvrnr_lender afsd
gen year = yofd(afsd) if month(afsd) > 3
replace year = yofd(afsd)-1 if month(afsd) <= 3


////////////////////////////////////////////
//////////   Deal with consolidated vs unconsolidated statements

bysort cvrnr_lender year: gen nr_statements= _N  // number of statements per lender-year

tab nr_statements typ
bys cvrnr_lender year: gen cons_statement = 1 if typ == "KON" 
bys cvrnr_lender year: egen has_cons_statement = max(cons_statement) 
bys cvrnr_lender year: egen nr_cons_statement = total(cons_statement) 
replace has_cons_statement = 0 if mi(has_cons_statement)

tab nr_statements has_cons_statement if nr_statements > 1
count  if nr_statements > 1 & nr_cons_statement > 1

// Drop firms with multiple consolidated statements in a year 
drop if nr_cons_statement > 1

// If firm has both consolidated and unconsolidated, keep consolidated 
drop if nr_statements > 1 & has_cons_statement == 1 & typ == "NOR"

drop nr_statements
bysort cvrnr_lender year: gen nr_statements= _N  // number of statements per lender-year

// Remaining obs with multiple statements are all due to them changing their accounting year 
sort cvrnr_lender afsd
order cvrnr_lender regaar afsd year typ 

// If two reports in the same year, keep latter one
bys cvrnr_lender year: egen max_month = max(month(afsd))
drop if nr_statements > 1 & max_month > month(afsd)

drop nr_statements
bysort cvrnr_lender year: gen nr_statements= _N  // number of statements per lender-year
tab nr_statements

drop nr_statements max_* has_c* nr_cons*


////////////////////////////////////////////
//////////   Join datasets
////////////////////////////////////////////

rename egenk TotalEquity 
rename lang LongTermDebt
rename kort ShortTermDebt 
rename bala TotalAssets


////////////////////////////////////////////
//////////  Deflate balance sheet items
////////////////////////////////////////////

merge m:1 year using "${rawdata}/DK_USD.dta", keep(match) nogen keepusing(cpi)

local vars_defl TotalEquity ShortTermDebt LongTermDebt TotalAssets 
	
foreach var in `vars_defl' {
		replace `var' = `var' * cpi / 100
	}


////////////////////////////////////////////
//////////   Data cleaning
////////////////////////////////////////////

** Focus on lenders with at least DKK 750,000 (EUR 100,000) total assets
keep if TotalAssets * 1000 > 750000

drop if mi(nonbank_lender)


////////////////////////////////////////////
//////////  Add and standardize Monetary Policy Shocks 
////////////////////////////////////////////

merge m:1 year using "${procdata}/macro_data.dta", nogenerate keep(match master)

local mpshocks JK_mpshocksign JK_eureon3m_hf AL_OIS3M_mpshock AL_OIS1Y_mpshock  ///
	AL_OIS10Y_mpshock AL_DE10Y_mpshock
	
foreach shock in `mpshocks' {

		qui rename `shock' raw_`shock'
		qui egen `shock' = std(raw_`shock')
		
	}

su `mpshocks' // All MPshocks are now standardized
su raw_* // raw values of all series (in %) are variables ending in _raw


////////////////////////////////////////////
//////////  Create important funding variables 
////////////////////////////////////////////

gen LTfunding = TotalAssets - ShortTermDebt

egen id = group(cvrnr_lender)
xtset id year 
local fundingvars TotalEquity ShortTermDebt LongTermDebt LTfunding  

qui foreach var of local fundingvars {
		gen `var'_gr = d.`var' / L.`var' 
		winsor2 `var'_gr, cuts(2 98) 
		
		gen `var'_ratio = `var' / TotalAssets
	}

*label vars
label var  ShortTermDebt_gr_w	"Short-term debt (yoy growth)"
label var  LongTermDebt_gr_w	"Long-term debt (yoy growth)"
label var  TotalEquity_gr_w		"Equity (yoy growth)"
label var  LTfunding_gr_w		"Long-term funding (yoy growth)"
label var  ShortTermDebt_ratio	"Short-term debt/Total assets"
label var  LongTermDebt_ratio	"Long-term debt/Total assets"
label var  TotalEquity_ratio	"Equity/Total assets"
label var  LTfunding_ratio		"Long-term funding/Total assets"
	
	
////////////////////////////////////////////
//////////   Save data
////////////////////////////////////////////

keep if inrange(year,2003,2015)

local macro_ctrls DK_real_gdp_grate DK_gdp_growth_forecast DK_inflation_rate  vix
					
keep cvrnr* year `mpshocks' `fundingvars' `macro_ctrls' *_ratio *_gr_w TotalAssets nonbank*

order year cvrnr_lender  nonbank* TotalAssets TotalEquity  
gsort cvrnr_lender year

* Save 
compress
save "${procdata}/final_Experian.dta", replace 